package com.ycxh.module.dbtoword;


import com.ycxh.controller.FileController;
import com.ycxh.module.dbtoword.domain.DbConfig;
import com.ycxh.module.dbtoword.domain.Tables;
import com.ycxh.module.dbtoword.util.JdbcMysql;
import com.ycxh.module.dbtoword.util.TableToWordUtil;
import com.ycxh.util.ViewResult;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.sql.Connection;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Slf4j
@RestController
@RequestMapping("/db")
public class DbController {

    @Autowired
    private DbConfig dbConfig;

    @Autowired
    private TableToWordUtil tableToWordUtil;

    /**
     * 导出数据库文档
     * http://127.0.0.1:8088/deploy/db/tableToWord
     */
    @GetMapping(value = "/tableToWord", produces = "application/json;charset=utf-8", name = "导出数据库表信息生成Word")
    public void tableToWord() {
        // 获取数据库表格数据
        // 1、获取数据库所有表信息
        JdbcMysql jdbcMysql = new JdbcMysql(dbConfig.getDatasourceUrl(), dbConfig.getUserName(), dbConfig.getPassword());
        List<Tables> tables = jdbcMysql.getAllTables(dbConfig.getDatabase());

        // 2、生成文件名信息 - 年月日时分秒
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
        String date = sdf.format(new Date());

        // 生成文件位置
        String docFileName = dbConfig.getFileName() + "-" + date + ".doc";
        String docFilePath = dbConfig.getFilePath() + File.separator + docFileName;

        // 3、调用工具类生成文件
        tableToWordUtil.toWord(null, null, tables, dbConfig.getFilePath(), dbConfig.getDatabase(), docFileName, dbConfig.getFileName());

        // 4、返回文件地址
        String filePath = docFilePath.replaceAll("\\\\", "/");
        System.out.println(filePath);
    }

    /**
     * 导出数据库文档 外部指定账号密码信息
     * http://127.0.0.1:8088/deploy/db/tableToWord
     */
    @PostMapping(value = "/tableToWordOut", produces = "application/json;charset=utf-8", name = "导出数据库表信息生成Word")
    public void tableToWordOut(@RequestBody Map map, HttpServletRequest request, HttpServletResponse response) throws Exception {
        // 获取数据库表格数据
        String host = (String) map.get("host"); // 主机IP
        Integer port = (Integer) map.get("port"); // 端口
        String userName = (String) map.get("userName"); // 用户名
        String password = (String) map.get("password"); // 密码
        String dbName = (String) map.get("dbName"); // 数据库名称
        String projectName = (String) map.get("projectName"); // 项目名称
        if (port == null) {
            port = 3306;
        }
        if (host == null || userName == null || password == null || dbName == null) {
            // 中文乱码
            response.setContentType("text/javascript; charset=utf-8");
            response.getWriter().write(ViewResult.newInstance().code(-1).msg("参数缺失").json());
            return;
        }

        String datasourceUrlTmp = "jdbc:mysql://{0}:{1}/mysql?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&nullCatalogMeansCurrent=true&databaseTerm=SCHEMA&connectTimeout=5000";
        // jdbc:mysql://127.0.0.1:3,306/mysql?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&nullCatalogMeansCurrent=true&databaseTerm=SCHEMA
        // java.text.MessageFormat格式化数字时会出现逗号分隔
        // String datasourceUrl = MessageFormat.format(datasourceUrlTmp, host, port);
        String datasourceUrl = MessageFormat.format(datasourceUrlTmp, host, port+"");

        // 1、获取数据库所有表信息
        JdbcMysql jdbcMysql = new JdbcMysql(datasourceUrl, userName, password);
        Connection conn = jdbcMysql.getConn();
        if (conn == null) {
            response.setContentType("text/javascript; charset=utf-8");
            response.getWriter().write(ViewResult.newInstance().code(-1).msg("创建数据库连接失败").json());
            return;
        }

        List<Tables> tables = jdbcMysql.getAllTables(dbName, conn);

        // 2、生成文件名信息 - 年月日时分秒
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
        String date = sdf.format(new Date());

        // 生成文件位置
        String fileName = StringUtils.isBlank(projectName) ? dbConfig.getFileName() : projectName;
        String docFileName = fileName + "-" + date + ".doc";
        String docFilePath = dbConfig.getFilePath() + File.separator + docFileName;

        // 3、调用工具类生成文件
        tableToWordUtil.toWord(jdbcMysql, conn, tables, dbConfig.getFilePath(), dbName, docFileName, fileName);

        // 4、返回文件地址
        String filePath = docFilePath.replaceAll("\\\\", "/");
        System.out.println(filePath);

        File file = new File(filePath);
        FileController.responseFile(file, docFileName, request, response);
        if (file.exists()) {
            file.delete();
        }
    }

}
